package com.ruoyi.business.sundry.service.impl;


import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.ObjUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.google.common.collect.Maps;
import com.ruoyi.business.base.JoBaseServiceImpl;
import com.ruoyi.business.base.PageParam;
import com.ruoyi.business.rule.domain.JbRules;
import com.ruoyi.business.rule.service.JbRulesService;
import com.ruoyi.business.sundry.domain.JbPerformance;
import com.ruoyi.business.sundry.domain.vo.*;
import com.ruoyi.business.sundry.mapper.JbPerformanceMapper;
import com.ruoyi.business.sundry.service.JbPerformanceService;
import com.ruoyi.business.sundry.service.SysDataScopeFactoryService;
import com.ruoyi.common.annotation.Excel;
import com.ruoyi.common.constant.Constants;
import com.ruoyi.common.core.domain.entity.SysDept;
import com.ruoyi.common.core.domain.entity.SysUser;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.system.service.ISysDeptService;
import com.ruoyi.system.service.ISysUserService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.compress.utils.Lists;
import org.apache.commons.lang3.ObjectUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

/**
 * 绩效Service业务层处理
 *
 * @author ruoyi
 * @date 2024-10-11
 */
@Service
@Slf4j
public class JbPerformanceServiceImpl
        extends JoBaseServiceImpl<JbPerformanceMapper, JbPerformance>
        implements JbPerformanceService
{
    @Autowired
    private ISysUserService sysUserService;
    @Autowired
    private ISysDeptService sysDeptService;
    @Autowired
    private SysDataScopeFactoryService sysDataScopeFactoryService;
    @Autowired
    private JbRulesService jbRulesService;

    @Override
    public BigDecimal getTargetTotal(JbPerformance jbPerformance)
    {
        BigDecimal targetTotal = BigDecimal.ZERO;
        // 最后确认时添加时的目标总和
        //角色数据权限
        List<Long> roleData=sysDataScopeFactoryService.fnListDataScope(Constants.PUBLIC_NUMBER_0.toString());
        QueryWrapper<JbPerformance> queryWrapper = new QueryWrapper<>();
        queryWrapper.select(" sum(annual_target) as annualTarget ");
        queryWrapper.lambda().eq(StringUtils.isNotBlank(jbPerformance.getTargetType())
                        , JbPerformance::getTargetType, jbPerformance.getTargetType())
                .eq(ObjectUtils.isNotEmpty(jbPerformance.getPerformanceType())
                        , JbPerformance::getPerformanceType, jbPerformance.getPerformanceType())
                .eq(StringUtils.isNotBlank(jbPerformance.getFinancialYear())
                        , JbPerformance::getFinancialYear, jbPerformance.getFinancialYear())
                .in(ObjectUtils.isNotEmpty(roleData), JbPerformance::getCreateBy,roleData);
        JbPerformance one = getOne(queryWrapper, false);
        if (ObjectUtils.isNotEmpty(one))
        {
            targetTotal = one.getAnnualTarget();
        }

        // 下面暂时先隐藏
//        switch (jbPerformance.getTargetType())
//        {
//            case Constants.CUSTOMER_TYPE_1001:
//                //成交金额
//                targetTotal = getOrder(jbPerformance);
//                break;
//            case Constants.CUSTOMER_TYPE_1002:
//                //回款金额
//                targetTotal = getReceived(jbPerformance);
//                break;
//            case Constants.CUSTOMER_TYPE_1003:
//                //跟进次数
//                targetTotal = getFollowupRecordCount(jbPerformance);
//                break;
//            case Constants.CUSTOMER_TYPE_1004:
//                //新增客户
//                targetTotal = getCustomerCount(jbPerformance);
//                break;
//            case Constants.CUSTOMER_TYPE_1005:
//                //新增商机
//                targetTotal = getOpportCount(jbPerformance);
//                break;
//            default:
//        }
        return targetTotal;
    }

    /**
     * 成交金额总和
     *
     * @param jbPerformance
     * @return
     */
    private BigDecimal getOrder(JbPerformance jbPerformance)
    {
        StringJoiner sql = new StringJoiner("");
        if (Constants.PUBLIC_NUMBER_2.equals(jbPerformance.getPerformanceType()))
        {
            // 财务审批完的订单
            sql.add(String.format("""
                            SELECT
                            	SUM( jo.order_total_amount ) annualTarget\s
                            FROM
                            	jb_order jo
                            	INNER JOIN (
                            	SELECT DISTINCT
                            		jp.related_id\s
                            	FROM
                            		jb_performance jp\s
                            	WHERE
                            		jp.del_flag = 0\s
                            		AND jp.financial_year = '%s'\s
                            		AND jp.target_type = '%s'\s
                            		AND jp.performance_type = %s\s
                            	) t ON jo.create_by = t.related_id\s
                            WHERE
                            	jo.del_flag = 0
                            	and jo.is_process_end='1'
                            	and YEAR(jo.create_time)='%s'
                            """, jbPerformance.getFinancialYear(), jbPerformance.getTargetType()
                    , jbPerformance.getPerformanceType(), jbPerformance.getFinancialYear()));
        } else if (Constants.PUBLIC_NUMBER_1.equals(jbPerformance.getPerformanceType()))
        {
            sql.add(String.format("""
                            SELECT
                                SUM( jo.order_total_amount ) annualTarget\s
                            FROM
                                jb_order jo
                                INNER JOIN sys_user su on jo.create_by=su.user_id
                                INNER JOIN (
                                SELECT DISTINCT
                                    jp.department\s
                                FROM
                                    jb_performance jp\s
                                WHERE
                                    jp.del_flag = 0\s
                                    AND jp.financial_year = '%s'\s
                                    AND jp.target_type = '%s'\s
                                    AND jp.performance_type = %s\s
                                ) t ON su.dept_id = t.department\s
                            WHERE
                                jo.del_flag = 0
                            	and jo.is_process_end='1'
                                and YEAR(jo.create_time)='%s'
                            """, jbPerformance.getFinancialYear(), jbPerformance.getTargetType()
                    , jbPerformance.getPerformanceType(), jbPerformance.getFinancialYear()));
        }
        return getBigDecimal(sql);
    }

    /**
     * 回款总和
     *
     * @param jbPerformance
     * @return
     */
    private BigDecimal getReceived(JbPerformance jbPerformance)
    {
        StringJoiner sql = new StringJoiner("");
        if (Constants.PUBLIC_NUMBER_2.equals(jbPerformance.getPerformanceType()))
        {
            sql.add(String.format("""
                            SELECT
                            	SUM( jo.payment_amount ) annualTarget
                            FROM
                            	jb_payment_received jo
                            	INNER JOIN (
                            	SELECT DISTINCT
                            		jp.related_id\s
                            	FROM
                            		jb_performance jp\s
                            	WHERE
                            		jp.del_flag = 0\s
                            		AND jp.financial_year = '%s'\s
                            		AND jp.target_type = '%s'\s
                            		AND jp.performance_type = %s\s
                            	) t ON jo.create_by = t.related_id\s
                            WHERE
                            	jo.del_flag = 0
                            	and YEAR(jo.create_time)='%s'
                            """, jbPerformance.getFinancialYear(), jbPerformance.getTargetType()
                    , jbPerformance.getPerformanceType(), jbPerformance.getFinancialYear()));
        } else if (Constants.PUBLIC_NUMBER_1.equals(jbPerformance.getPerformanceType()))
        {
            sql.add(String.format("""
                            SELECT
                            	SUM( jo.payment_amount ) annualTarget
                            FROM
                            	jb_payment_received jo
                            	INNER JOIN sys_user su on jo.create_by=su.user_id
                            	INNER JOIN (
                            	SELECT DISTINCT
                            		jp.department\s
                            	FROM
                            		jb_performance jp\s
                            	WHERE
                            		jp.del_flag = 0\s
                            		AND jp.financial_year = '%s'\s
                            		AND jp.target_type = '%s'\s
                            		AND jp.performance_type = %s\s
                            	) t ON su.dept_id = t.department\s
                            WHERE
                            	jo.del_flag = 0
                            	and YEAR(jo.create_time)='%s'
                            """, jbPerformance.getFinancialYear(), jbPerformance.getTargetType()
                    , jbPerformance.getPerformanceType(), jbPerformance.getFinancialYear()));
        }
        return getBigDecimal(sql);
    }

    /**
     * 跟进记录总数
     *
     * @param jbPerformance
     * @return
     */
    private BigDecimal getFollowupRecordCount(JbPerformance jbPerformance)
    {
        StringJoiner sql = new StringJoiner("");
        if (Constants.PUBLIC_NUMBER_2.equals(jbPerformance.getPerformanceType()))
        {
            sql.add(String.format("""
                            SELECT
                            	count(1) annualTarget
                            FROM
                            	jb_followup_record jo
                            	INNER JOIN (
                            	SELECT DISTINCT
                            		jp.related_id\s
                            	FROM
                            		jb_performance jp\s
                            	WHERE
                            		jp.del_flag = 0\s
                            		AND jp.financial_year = '%s'\s
                            		AND jp.target_type = '%s'\s
                            		AND jp.performance_type = %s\s
                            	) t ON jo.create_by = t.related_id\s
                            WHERE
                            	jo.del_flag = 0
                            	and jo.data_type=1
                            	and YEAR(jo.create_time)='%s'
                            """, jbPerformance.getFinancialYear(), jbPerformance.getTargetType()
                    , jbPerformance.getPerformanceType(), jbPerformance.getFinancialYear()));
        } else if (Constants.PUBLIC_NUMBER_1.equals(jbPerformance.getPerformanceType()))
        {
            sql.add(String.format("""
                            SELECT
                            	count(1) annualTarget
                            FROM
                            	jb_followup_record jo
                            	INNER JOIN sys_user su on jo.create_by=su.user_id
                            	INNER JOIN (
                            	SELECT DISTINCT
                            		jp.department\s
                            	FROM
                            		jb_performance jp\s
                            	WHERE
                            		jp.del_flag = 0\s
                            		AND jp.financial_year = '%s'\s
                            		AND jp.target_type = '%s'\s
                            		AND jp.performance_type = %s\s
                            	) t ON su.dept_id = t.department\s
                            WHERE
                            	jo.del_flag = 0
                            	and jo.data_type=1
                            	and YEAR(jo.create_time)='%s'
                            """, jbPerformance.getFinancialYear(), jbPerformance.getTargetType()
                    , jbPerformance.getPerformanceType(), jbPerformance.getFinancialYear()));
        }
        return getBigDecimal(sql);
    }

    /**
     * 客户总数
     *
     * @param jbPerformance
     * @return
     */
    private BigDecimal getCustomerCount(JbPerformance jbPerformance)
    {
        StringJoiner sql = new StringJoiner("");
        if (Constants.PUBLIC_NUMBER_2.equals(jbPerformance.getPerformanceType()))
        {
            sql.add(String.format("""
                            SELECT
                            	count(1) annualTarget
                            FROM
                            	jb_customer jo
                            	INNER JOIN (
                            	SELECT DISTINCT
                            		jp.related_id\s
                            	FROM
                            		jb_performance jp\s
                            	WHERE
                            		jp.del_flag = 0\s
                            		AND jp.financial_year = '%s'\s
                            		AND jp.target_type = '%s'\s
                            		AND jp.performance_type = %s\s
                            	) t ON jo.create_by = t.related_id\s
                            WHERE
                            	jo.del_flag = 0
                            	and YEAR(jo.create_time)='%s'
                            """, jbPerformance.getFinancialYear(), jbPerformance.getTargetType()
                    , jbPerformance.getPerformanceType(), jbPerformance.getFinancialYear()));
        } else if (Constants.PUBLIC_NUMBER_1.equals(jbPerformance.getPerformanceType()))
        {
            sql.add(String.format("""
                            SELECT
                            	count(1) annualTarget
                            FROM
                            	jb_customer jo
                            	INNER JOIN sys_user su on jo.create_by=su.user_id
                            	INNER JOIN (
                            	SELECT DISTINCT
                            		jp.department\s
                            	FROM
                            		jb_performance jp\s
                            	WHERE
                            		jp.del_flag = 0\s
                            		AND jp.financial_year = '%s'\s
                            		AND jp.target_type = '%s'\s
                            		AND jp.performance_type = %s\s
                            	) t ON su.dept_id = t.department\s
                            WHERE
                            	jo.del_flag = 0
                            	and YEAR(jo.create_time)='%s'
                            """, jbPerformance.getFinancialYear(), jbPerformance.getTargetType()
                    , jbPerformance.getPerformanceType(), jbPerformance.getFinancialYear()));
        }
        return getBigDecimal(sql);
    }

    /**
     * 商机总数
     *
     * @param jbPerformance
     * @return
     */
    private BigDecimal getOpportCount(JbPerformance jbPerformance)
    {
        StringJoiner sql = new StringJoiner("");
        if (Constants.PUBLIC_NUMBER_2.equals(jbPerformance.getPerformanceType()))
        {
            sql.add(String.format("""
                            SELECT
                            	count(1) annualTarget
                            FROM
                            	jb_opportunity jo
                            	INNER JOIN (
                            	SELECT DISTINCT
                            		jp.related_id\s
                            	FROM
                            		jb_performance jp\s
                            	WHERE
                            		jp.del_flag = 0\s
                            		AND jp.financial_year = '%s'\s
                            		AND jp.target_type = '%s'\s
                            		AND jp.performance_type = %s\s
                            	) t ON jo.create_by = t.related_id\s
                            WHERE
                            	jo.del_flag = 0
                            	and YEAR(jo.create_time)='%s'
                            """, jbPerformance.getFinancialYear(), jbPerformance.getTargetType()
                    , jbPerformance.getPerformanceType(), jbPerformance.getFinancialYear()));
        } else if (Constants.PUBLIC_NUMBER_1.equals(jbPerformance.getPerformanceType()))
        {
            sql.add(String.format("""
                            SELECT
                            	count(1) annualTarget
                            FROM
                            	jb_opportunity jo
                            	INNER JOIN sys_user su on jo.create_by=su.user_id
                            	INNER JOIN (
                            	SELECT DISTINCT
                            		jp.department\s
                            	FROM
                            		jb_performance jp\s
                            	WHERE
                            		jp.del_flag = 0\s
                            		AND jp.financial_year = '%s'\s
                            		AND jp.target_type = '%s'\s
                            		AND jp.performance_type = %s\s
                            	) t ON su.dept_id = t.department\s
                            WHERE
                            	jo.del_flag = 0
                            	and YEAR(jo.create_time)='%s'
                            """, jbPerformance.getFinancialYear(), jbPerformance.getTargetType()
                    , jbPerformance.getPerformanceType(), jbPerformance.getFinancialYear()));
        }
        return getBigDecimal(sql);
    }

    private BigDecimal getBigDecimal(StringJoiner sql)
    {
        List<JbPerformance> jbPerformances = joSqlUtil.jsonToEntity(baseMapper.findJson(sql.toString()), JbPerformance.class);
        if (ObjectUtils.isNotEmpty(jbPerformances) && ObjectUtils.isNotEmpty(jbPerformances.get(0))
                && ObjectUtils.isNotEmpty(jbPerformances.get(0).getAnnualTarget()))
        {
            return jbPerformances.get(0).getAnnualTarget();
        }
        return BigDecimal.ZERO;
    }

    @Override
    public IPage<JbPerformanceVo> listPage(PageParam page, JbPerformance jbPerformance)
    {
        //角色数据权限
        List<Long> roleData=sysDataScopeFactoryService.fnListDataScope(Constants.PUBLIC_NUMBER_0.toString());
        LambdaQueryWrapper<JbPerformance> select = new LambdaQueryWrapper<>();
        select.eq(StringUtils.isNotBlank(jbPerformance.getFinancialYear()),
                        JbPerformance::getFinancialYear, jbPerformance.getFinancialYear())
                .eq(StringUtils.isNotBlank(jbPerformance.getTargetType()),
                        JbPerformance::getTargetType, jbPerformance.getTargetType())
                .eq(ObjectUtils.isNotEmpty(jbPerformance.getPerformanceType()),
                        JbPerformance::getPerformanceType, jbPerformance.getPerformanceType())
                .in(ObjectUtils.isNotEmpty(roleData), JbPerformance::getRelatedId,roleData);
        IPage<JbPerformance> iPage = page(page.b(), select);
        List<Long> userIds = Lists.newArrayList();
        List<Long> deptIds = Lists.newArrayList();
        IPage<JbPerformanceVo> pageVo = iPage.convert(a ->
        {
            userIds.add(a.getRelatedId());
            deptIds.add(a.getDepartment());
            JbPerformanceVo vo = BeanUtil.toBean(a, JbPerformanceVo.class);
            return vo;
        });
        if (ObjectUtils.isNotEmpty(pageVo) && ObjectUtils.isNotEmpty(pageVo.getRecords())
                && (Constants.PUBLIC_NUMBER_2.equals(jbPerformance.getPerformanceType()) ||
                Constants.PUBLIC_NUMBER_1.equals(jbPerformance.getPerformanceType())))
        {
            // 个人和部门需要处理换成名字
            Map<Long, String> userCollect = Maps.newHashMap();
            Map<Long, String> deptCollect = Maps.newHashMap();
            if (ObjectUtils.isNotEmpty(userIds))
            {
                List<SysUser> userList = sysUserService.list(Wrappers.lambdaQuery(SysUser.class).in(SysUser::getUserId, userIds)
                        .select(SysUser::getUserId, SysUser::getUserName));
                userCollect = userList.stream().collect(Collectors.toMap(SysUser::getUserId, SysUser::getUserName));
            }
            if (ObjectUtils.isNotEmpty(deptIds))
            {
                List<SysDept> deptList = sysDeptService.list(Wrappers.lambdaQuery(SysDept.class).in(SysDept::getDeptId, deptIds)
                        .select(SysDept::getDeptId, SysDept::getDeptName));
                deptCollect = deptList.stream().collect(Collectors.toMap(SysDept::getDeptId, SysDept::getDeptName));
            }
            for (JbPerformanceVo a : pageVo.getRecords())
            {
                a.setUserName(userCollect.get(a.getRelatedId()));
                a.setDeptName(deptCollect.get(a.getDepartment()));
            }
        }
        return pageVo;
    }

    @Override
    public Boolean saveInfo(JbPerformanceVo jbPerformances)
    {
        List<JbPerformance> adds = Lists.newArrayList();
        if (Constants.PUBLIC_NUMBER_2.equals(jbPerformances.getPerformanceType()))
        {
            // 个人
            List<SysUser> userList = sysUserService.list(Wrappers.lambdaQuery(SysUser.class).in(SysUser::getUserId, jbPerformances.getSelectUserIds())
                    .select(SysUser::getUserId, SysUser::getDeptId));
            Map<Long, Long> deptIds = userList.stream().collect(Collectors.toMap(SysUser::getUserId, SysUser::getDeptId));
            jbPerformances.getSelectUserIds().forEach(a ->
            {
                JbPerformance performances = BeanUtil.toBean(jbPerformances, JbPerformance.class);
                performances.setRelatedId(a);
                performances.setDepartment(deptIds.get(a));
                adds.add(performances);
            });
        } else if (Constants.PUBLIC_NUMBER_1.equals(jbPerformances.getPerformanceType()))
        {
            // 部门
            jbPerformances.getSelectUserIds().forEach(a ->
            {
                JbPerformance performances = BeanUtil.toBean(jbPerformances, JbPerformance.class);
                performances.setDepartment(a);
                adds.add(performances);
            });
        } else if (Constants.PUBLIC_NUMBER_0.equals(jbPerformances.getPerformanceType()))
        {
            // 公司
            adds.add(BeanUtil.toBean(jbPerformances, JbPerformance.class));
        }

        return saveBatch(adds);
    }

    @Override
    public IPage<JbUserPerformanceVo> userPerformancePage(PageParam page, JbUserPerformanceVo jbPerformance)
    {
        String userPerformanSql = getUserPerformanSql(jbPerformance);
        IPage<JbUserPerformanceVo> jbUserPerformanceVoIPage = joSqlUtil.jsonToEntity(baseMapper.pageJson(page.b()
                , userPerformanSql), JbUserPerformanceVo.class);

        return jbUserPerformanceVoIPage;
    }


    @Override
    public List<JbUserPerformanceVo> exportUserPerformance(JbUserPerformanceVo jbPerformance)
    {
        String userPerformanSql = getUserPerformanSql(jbPerformance);
        List<JbUserPerformanceVo> list = joSqlUtil.jsonToEntity(baseMapper.findJson(
                userPerformanSql), JbUserPerformanceVo.class);
        list.forEach(a -> a.setConversionRateExport(a.getConversionRate() + "%"));
        return list;
    }

    @Override
    public IPage<JbUserCommissionVo> userCommissionPage(PageParam page, JbUserCommissionVo jbUserCommissionVo)
    {
        String commissionVoSql = getCommissionVoSql(jbUserCommissionVo);
        IPage<JbUserCommissionVo> jbUserPerformanceVoIPage = joSqlUtil.jsonToEntity(baseMapper.pageJson(page.b()
                , commissionVoSql), JbUserCommissionVo.class);
        if (ObjectUtils.isNotEmpty(jbUserPerformanceVoIPage) && ObjectUtils.isNotEmpty(jbUserPerformanceVoIPage.getRecords()))
        {
            //获取提成规则配置的奖金比例
            //查询规则
            JbRules jbRules = jbRulesService.getOne(new LambdaQueryWrapper<JbRules>().eq(JbRules::getRulesCode,
                    "royalties"));
            JSONObject jbRoyaltiesJSON = JSONObject.parseObject(jbRules.getExecutionContent().toString());
            //获取performanceRatio，转成list<Map>
            List<Map> performanceRatioList = JSONArray.parseArray(jbRoyaltiesJSON.getString("performanceRatio")
                    , Map.class);
            jbUserPerformanceVoIPage.getRecords().forEach(a -> a.getCommissionCalculation(performanceRatioList));
        }
        return jbUserPerformanceVoIPage;
    }

    @Override
    public JbUserCommissionVo userCommissionPageSum(JbUserCommissionVo jbUserCommissionVo)
    {
        JbUserCommissionVo vo=new JbUserCommissionVo();
        String commissionVoSql = getCommissionVoSql(jbUserCommissionVo);
        List<JbUserCommissionVo> jbUserPerformanceVoIPage = joSqlUtil.jsonToEntity(baseMapper.findJson(commissionVoSql), JbUserCommissionVo.class);
        if (ObjectUtils.isNotEmpty(jbUserPerformanceVoIPage) && ObjectUtils.isNotEmpty(jbUserPerformanceVoIPage))
        {
            //获取提成规则配置的奖金比例
            //查询规则
            JbRules jbRules = jbRulesService.getOne(new LambdaQueryWrapper<JbRules>().eq(JbRules::getRulesCode,
                    "royalties"));
            JSONObject jbRoyaltiesJSON = JSONObject.parseObject(jbRules.getExecutionContent().toString());
            //获取performanceRatio，转成list<Map>
            List<Map> performanceRatioList = JSONArray.parseArray(jbRoyaltiesJSON.getString("performanceRatio")
                    , Map.class);
            jbUserPerformanceVoIPage.forEach(a -> {
                a.getCommissionCalculation(performanceRatioList);
                vo.setOrderTotalAmount(vo.getOrderTotalAmount().add(a.getOrderTotalAmount()));
                vo.setOrderReceivedAmount(vo.getOrderReceivedAmount().add(a.getOrderReceivedAmount()));
                vo.setCommissionAmount(vo.getCommissionAmount().add(a.getCommissionAmount()));
                vo.setCommissionLastAmount(vo.getCommissionLastAmount().add(a.getCommissionLastAmount()));
            });
        }

        return vo;
    }


    @Override
    public List<JbUserCommissionVo> exportCommission(JbUserCommissionVo jbUserCommissionVo)
    {
        String commissionVoSql = getCommissionVoSql(jbUserCommissionVo);
        List<JbUserCommissionVo> list = joSqlUtil.jsonToEntity(baseMapper.findJson(
                commissionVoSql), JbUserCommissionVo.class);
        if (ObjectUtils.isNotEmpty(list))
        {
            //获取提成规则配置的奖金比例
            //查询规则
            JbRules jbRules = jbRulesService.getOne(new LambdaQueryWrapper<JbRules>().eq(JbRules::getRulesCode,
                    "royalties"));
            JSONObject jbRoyaltiesJSON = JSONObject.parseObject(jbRules.getExecutionContent().toString());
            //获取performanceRatio，转成list<Map>
            List<Map> performanceRatioList = JSONArray.parseArray(jbRoyaltiesJSON.getString("performanceRatio")
                    , Map.class);
            list.forEach(a -> a.getCommissionCalculation(performanceRatioList));
        }
        return list;
    }

    @Override
    public IPage<PerformanceRankingVo> performanceRanking(PageParam pageParam)
    {
        //财务审批完的订单
        // 默认本年的开始时间
        String type=pageParam.getKeyword();
        LocalDateTime[] times = DateUtils.getTimes(Constants.CUSTOMER_TYPE_1001);
        if (Constants.CUSTOMER_TYPE_1009.equals(type))
        {
            times = DateUtils.getTimes(Constants.CUSTOMER_TYPE_1009);
        }else if (Constants.CUSTOMER_TYPE_1005.equals(type))
        {
            times = DateUtils.getTimes(Constants.CUSTOMER_TYPE_1005);
        }
        LocalDateTime now = LocalDateTime.now();
        StringJoiner sql = new StringJoiner("");
        sql.add(String.format("""
                SELECT
                	su.user_id,
                	su.user_name as userName,
                	sum( order_total_amount ) AS orderTotalAmount\s
                	,(select jp.annual_target from jb_performance jp where jp.del_flag=0\s
                	and jp.performance_type=2 and target_type='1001' and jp.financial_year='%s'
                	and jp.related_id=su.user_id LIMIT 1) as annualTarget
                	,(select jp.target_unit from jb_performance jp where jp.del_flag=0\s
                	and jp.performance_type=2 and target_type='1001' and jp.financial_year='%s'
                	and jp.related_id=su.user_id LIMIT 1) as targetUnit
                FROM
                	sys_user su
                	INNER JOIN jb_order jo ON su.user_id = jo.create_by\s
                WHERE
                	su.del_flag = 0\s
                	AND jo.del_flag = 0\s
                	and jo.is_process_end='1'
                	AND jo.create_time>= '%s'\s
                	AND jo.create_time<= '%s'\s
                GROUP BY
                	su.user_id\s
                ORDER BY
                	orderTotalAmount DESC\s
                """, now.getYear(), now.getYear(), times[0], times[1]));
        IPage<PerformanceRankingVo> page = joSqlUtil.jsonToEntity(baseMapper.pageJson(pageParam.b(), sql.toString())
                , PerformanceRankingVo.class);
        if (ObjectUtils.isNotEmpty(page) && ObjectUtils.isNotEmpty(page.getRecords()))
        {
            page.getRecords().forEach(a -> {
                String targetUnit = a.getTargetUnit();
                BigDecimal annualTargetTransform = BigDecimal.ZERO;
                BigDecimal annualTarget = a.getAnnualTarget();
                if (ObjUtil.isNotNull(annualTarget))
                {
                    //如果targetUnit单位是万，则把annualTarget乘以万
                    if (Constants.CUSTOMER_TYPE_1001.equals(targetUnit))
                    {
                        annualTargetTransform = annualTarget.multiply(new BigDecimal(10000));
                    }
                    if (Constants.CUSTOMER_TYPE_1002.equals(targetUnit))
                    {
                        annualTargetTransform = annualTarget.multiply(new BigDecimal(100000000));
                    }
                }
                a.setAnnualTargetTransform(annualTargetTransform);
            });
        }
        return page;
    }
    @Override
    public List<JbUserCommissionVo> performanceRankingData(PageParam page, JbUserCommissionVo jbUserCommissionVo)
    {

        String commissionVoSql = getCommissionVoSqlData(page,jbUserCommissionVo);
        List<JbUserCommissionVo> jbUserPerformanceVoIPage = joSqlUtil.jsonToEntity(baseMapper.findJson(
                 commissionVoSql), JbUserCommissionVo.class);
        // if (ObjectUtils.isNotEmpty(jbUserPerformanceVoIPage) && ObjectUtils.isNotEmpty(jbUserPerformanceVoIPage.getRecords()))
        // {
        //     //获取提成规则配置的奖金比例
        //     //查询规则
        //     JbRules jbRules = jbRulesService.getOne(new LambdaQueryWrapper<JbRules>().eq(JbRules::getRulesCode,
        //             "royalties"));
        //     JSONObject jbRoyaltiesJSON = JSONObject.parseObject(jbRules.getExecutionContent().toString());
        //     //获取performanceRatio，转成list<Map>
        //     List<Map> performanceRatioList = JSONArray.parseArray(jbRoyaltiesJSON.getString("performanceRatio")
        //             , Map.class);
        //     jbUserPerformanceVoIPage.getRecords().forEach(a -> a.getCommissionCalculation(performanceRatioList));
        // }
        return jbUserPerformanceVoIPage;
    }
    /**
     * 提成sql
     *
     * @param jbUserCommissionVo
     * @return
     */
    private String getCommissionVoSqlData(PageParam page,JbUserCommissionVo jbUserCommissionVo)
    {
        // 默认本年的开始时间
        String type=page.getKeyword();
        LocalDateTime[] times = DateUtils.getTimes(Constants.CUSTOMER_TYPE_1001);
        if (Constants.CUSTOMER_TYPE_1009.equals(type))
        {
            times = DateUtils.getTimes(Constants.CUSTOMER_TYPE_1009);
        }else if (Constants.CUSTOMER_TYPE_1005.equals(type))
        {
            times = DateUtils.getTimes(Constants.CUSTOMER_TYPE_1005);
        }
        //已完成和已结束订单
        StringJoiner sql = new StringJoiner(" ")
                .add(StringUtils.format("""
                        SELECT
                        	*\s
                        FROM
                        	(
                        	SELECT
                        		count( jo.id ) AS orderAdd,
                        		SUM( order_total_amount ) AS orderTotalAmount,
                        		SUM( received_amount + deposit_amount ) AS orderReceivedAmount,
                        		SUM( commission_amount ) AS commissionAmount,
                        		su.user_name\s
                        	FROM
                        		jb_order jo
                        		INNER JOIN sys_user su ON su.user_id = jo.create_by\s
                        	WHERE
                        		jo.del_flag = 0\s
                        		AND jo.is_process_end = '1'\s
                        		AND su.del_flag = 0\s
                        		AND jo.create_time >= '{}'\s
                        		AND jo.create_time <= '{}'\s
                        	GROUP BY
                        		jo.create_by\s
                        	) AS g1\s
                        ORDER BY
                        	g1.orderTotalAmount DESC
                        """, times[0], times[1]));

        return sql.toString();
    }
    @Override
    public IPage<JbStatisticsPerformanceVo> appPersonageList(PageParam param, JbPerformance jbPerformance)
    {
        jbPerformance.setFinancialYear(StringUtils.isNotBlank(jbPerformance.getFinancialYear())
                ? jbPerformance.getFinancialYear() : String.valueOf(LocalDateTime.now().getYear()));
        jbPerformance.setTargetType(StringUtils.isNotBlank(jbPerformance.getTargetType()) ? jbPerformance.getTargetType()
                : Constants.CUSTOMER_TYPE_1001);
        StringJoiner sql = new StringJoiner("");
        if (Constants.PUBLIC_NUMBER_1.equals(jbPerformance.getPerformanceType()))
        {
            //财务审批完的订单
            // 部门目标金额数据
            sql.add(String.format("""
                            SELECT
                                jp.department,
                                jor.dept_name,
                                jp.annual_target,
                                jor.totalAmount
                            FROM
                                jb_performance jp
                                LEFT JOIN (
                                    select sd.dept_id,sd.dept_name,sum( order_total_amount ) AS totalAmount from\s
                                     jb_order jo
                                    INNER  JOIN sys_user su ON jo.create_by = su.user_id
                                    INNER  JOIN sys_dept sd ON su.dept_id = sd.dept_id\s
                                    where jo.del_flag=0 and su.del_flag=0 and sd.del_flag=0
                                    and jo.is_process_end='1'
                                    AND YEAR ( jo.create_time )= %s\s
                                    GROUP BY sd.dept_id,sd.dept_name
                                ) jor on jp.department=jor.dept_id
                            WHERE
                                jp.del_flag = 0\s
                                AND jp.performance_type = 1\s
                                AND jp.financial_year = %s\s
                                AND jp.target_type=%s\s
                            GROUP BY
                                jp.department,
                                jp.annual_target,
                                jor.dept_name\s
                            ORDER BY
                                jor.totalAmount DESC
                            """, jbPerformance.getFinancialYear(), jbPerformance.getFinancialYear()
                    , jbPerformance.getTargetType()));
        } else
        {
            //财务审批完的订单
            // 个人目标金额
            sql.add(String.format("""
                            SELECT
                            	jp.related_id,
                            	su.user_name,
                            	sd.dept_name,
                            	jp.annual_target,
                            	sum( order_total_amount ) AS totalAmount\s
                            FROM
                            	jb_performance jp
                            	LEFT JOIN jb_order jo ON jp.related_id = jo.create_by
                            	LEFT JOIN sys_user su ON jo.create_by = su.user_id
                            	LEFT JOIN sys_dept sd ON su.dept_id = sd.dept_id\s
                            WHERE
                            	jp.del_flag = 0\s
                            	and is_process_end='1'
                            	AND jo.del_flag = 0\s
                            	AND su.del_flag = 0\s
                            	AND su.del_flag = 0\s
                            	AND jp.performance_type = 2\s
                            	AND YEAR ( jo.create_time )=%s
                            	AND jp.financial_year =%s\s
                            	AND jp.target_type=%s\s
                            GROUP BY
                            	jp.related_id,
                            	jp.annual_target,
                            	su.user_name,
                            	sd.dept_name\s
                            ORDER BY
                            	totalAmount DESC
                            """, jbPerformance.getFinancialYear(), jbPerformance.getFinancialYear()
                    , jbPerformance.getTargetType()));
        }
        IPage<JbStatisticsPerformanceVo> page = joSqlUtil.jsonToEntity(baseMapper.pageJson(param.b(), sql.toString()), JbStatisticsPerformanceVo.class);
        AtomicInteger serialNumber = new AtomicInteger(1 + (param.getPageNum() - 1) * param.getPageSize());
        page.getRecords().forEach(a ->
        {
            a.getConversionRateCompute();
            a.setRanking(serialNumber.getAndIncrement());
        });
        return page;
    }

    @Override
    public IPage<JbStatisticsPerformanceVo> appPersonageOrderList(PageParam param, JbPerformance jbPerformance)
    {
        StringJoiner sql = new StringJoiner("");
        if (Constants.PUBLIC_NUMBER_1.equals(jbPerformance.getPerformanceType()))
        {
            //财务审批完的订单
            // 部门
            sql.add(String.format("""
                    SELECT
                    	sd.dept_name,
                    	count(jo.id) as totalCount,
                    	sum( jo.order_total_amount ) AS totalAmount,
                     	(select count(DISTINCT commodity_id) from jb_order_commodity\s
                     	where del_flag=0 and FIND_IN_SET(order_id,GROUP_CONCAT(jo.id))) as commodityCount\s
                    FROM
                    	jb_order jo
                    	INNER JOIN sys_user su ON jo.create_by = su.user_id
                    	INNER JOIN sys_dept sd ON su.dept_id = sd.dept_id\s
                    WHERE
                    	jo.del_flag = 0\s
                    	and jo.is_process_end='1'
                    	AND su.del_flag = 0\s
                    	AND sd.del_flag = 0\s
                    	AND jo.create_time >= '%s'\s
                    	AND jo.create_time <= '%s'\s
                    GROUP BY
                    	sd.dept_name\s
                    ORDER BY
                    	totalAmount DESC
                    """, param.getStartTimeBuilder(), param.getEndTimeBuilder()));
        } else
        {
            //财务审批完的订单
            sql.add(String.format("""
                    SELECT
                    	jo.create_by,
                    	su.user_name,
                    	sd.dept_name,
                    	count(jo.id) as totalCount,
                    	sum( jo.order_total_amount ) AS totalAmount,
                     	(select count(DISTINCT commodity_id) from jb_order_commodity\s
                     	where del_flag=0 and FIND_IN_SET(order_id,GROUP_CONCAT(jo.id))) as commodityCount\s
                    FROM
                    	jb_order jo
                    	INNER JOIN sys_user su ON jo.create_by = su.user_id
                    	INNER JOIN sys_dept sd ON su.dept_id = sd.dept_id\s
                    WHERE
                    	jo.del_flag = 0\s
                        and jo.is_process_end='1'
                    	AND su.del_flag = 0\s
                    	AND sd.del_flag = 0\s
                    	AND jo.create_time >= '%s'\s
                    	AND jo.create_time <= '%s'\s
                    GROUP BY
                    	jo.create_by,
                    	su.user_name,
                    	sd.dept_name\s
                    ORDER BY
                    	totalAmount DESC
                    """, param.getStartTimeBuilder(), param.getEndTimeBuilder()));
        }
        IPage<JbStatisticsPerformanceVo> page = joSqlUtil.jsonToEntity(baseMapper.pageJson(param.b(), sql.toString())
                , JbStatisticsPerformanceVo.class);
        AtomicInteger serialNumber = new AtomicInteger(1 + (param.getPageNum() - 1) * param.getPageSize());
        page.getRecords().forEach(a ->
        {
            a.setRanking(serialNumber.getAndIncrement());
            a.setAverage(ObjectUtils.isNotEmpty(a.getCommodityCount())
                    ? a.getTotalAmount().divide(new BigDecimal(a.getCommodityCount())
                    , 2, BigDecimal.ROUND_HALF_UP) : BigDecimal.ZERO);
        });
        return page;
    }

    @Override
    public IPage<JbStatisticsPerformanceVo> appPersonageReceivablesList(PageParam param, JbPerformance jbPerformance)
    {
        StringJoiner sql = new StringJoiner("");
        if (Constants.PUBLIC_NUMBER_1.equals(jbPerformance.getPerformanceType()))
        {
            // 部门
            sql.add(String.format("""
                    SELECT
                    	sd.dept_name,
                    	count(jo.id) totalCount,
                    	sum( jo.payment_amount ) AS totalAmount\s
                    FROM
                    	jb_payment_received jo
                    	INNER JOIN sys_user su ON jo.create_by = su.user_id
                    	INNER JOIN sys_dept sd ON su.dept_id = sd.dept_id\s
                    WHERE
                    	jo.del_flag = 0\s
                    	AND su.del_flag = 0\s
                    	AND sd.del_flag = 0\s
                    	AND jo.create_time >= '%s'\s
                    	AND jo.create_time <= '%s'\s
                    GROUP BY
                    	sd.dept_name\s
                    ORDER BY
                    	totalAmount DESC
                    """, param.getStartTimeBuilder(), param.getEndTimeBuilder()));
        } else
        {
            // 个人
            sql.add(String.format("""
                    SELECT
                    	jo.create_by,
                    	su.user_name,
                    	sd.dept_name,
                    	count(jo.id) totalCount,
                    	sum( jo.payment_amount ) AS totalAmount\s
                    FROM
                    	jb_payment_received jo
                    	INNER JOIN sys_user su ON jo.create_by = su.user_id
                    	INNER JOIN sys_dept sd ON su.dept_id = sd.dept_id\s
                    WHERE
                    	jo.del_flag = 0\s
                    	AND su.del_flag = 0\s
                    	AND sd.del_flag = 0\s
                    	AND jo.create_time >= '%s'\s
                    	AND jo.create_time <= '%s'\s
                    GROUP BY
                    	jo.create_by,
                    	su.user_name,
                    	sd.dept_name\s
                    ORDER BY
                    	totalAmount DESC
                    """, param.getStartTimeBuilder(), param.getEndTimeBuilder()));
        }
        IPage<JbStatisticsPerformanceVo> page = joSqlUtil.jsonToEntity(baseMapper.pageJson(param.b(), sql.toString())
                , JbStatisticsPerformanceVo.class);
        AtomicInteger serialNumber = new AtomicInteger(1 + (param.getPageNum() - 1) * param.getPageSize());
        page.getRecords().forEach(a ->
        {
            a.setRanking(serialNumber.getAndIncrement());
        });
        return page;
    }

    @Override
    public List<JbStatisticsPerformanceVo> appBusinessTimeList(PageParam param, JbStatisticsPerformanceVo jbPerformance)
    {
        // 时间段内商机新增
        List<JbStatisticsPerformanceVo> jbOpportunity = appBusinessTimeListSql(param, jbPerformance
                , "jb_opportunity", StringUtils.EMPTY);
        // 时间段内客户新增
        List<JbStatisticsPerformanceVo> jbCustomer = appBusinessTimeListSql(param, jbPerformance
                , "jb_customer", StringUtils.EMPTY);
        // 时间段内订单新增
        List<JbStatisticsPerformanceVo> jbOrder = appBusinessTimeListSql(param, jbPerformance
                , "jb_order", StringUtils.EMPTY);
        Map<String, Integer> opMap = jbOpportunity.stream().collect(Collectors.toMap(
                JbStatisticsPerformanceVo::getTime, JbStatisticsPerformanceVo::getTotalCount));
        Map<String, Integer> cuMap = jbCustomer.stream().collect(Collectors.toMap(
                JbStatisticsPerformanceVo::getTime, JbStatisticsPerformanceVo::getTotalCount));
        Map<String, Integer> orMap = jbOrder.stream().collect(Collectors.toMap(
                JbStatisticsPerformanceVo::getTime, JbStatisticsPerformanceVo::getTotalCount));
        // 上面三个list提前time时间字段合并成一个list
        Set<String> time = new HashSet<>();
        time.addAll(jbOpportunity.stream().map(JbStatisticsPerformanceVo::getTime).collect(Collectors.toList()));
        time.addAll(jbCustomer.stream().map(JbStatisticsPerformanceVo::getTime).collect(Collectors.toList()));
        time.addAll(jbOrder.stream().map(JbStatisticsPerformanceVo::getTime).collect(Collectors.toList()));
        List<JbStatisticsPerformanceVo> list = new ArrayList<>();
        time.forEach(t ->
        {
            JbStatisticsPerformanceVo jbStatisticsPerformanceVo = new JbStatisticsPerformanceVo();
            jbStatisticsPerformanceVo.setTime(t);
            jbStatisticsPerformanceVo.setCustomerAdd(cuMap.get(t) == null ? 0 : cuMap.get(t));
            jbStatisticsPerformanceVo.setFollowUpAdd(opMap.get(t) == null ? 0 : opMap.get(t));
            jbStatisticsPerformanceVo.setOrderAdd(orMap.get(t) == null ? 0 : orMap.get(t));
            list.add(jbStatisticsPerformanceVo);
        });
        list.sort(Comparator.comparing(JbStatisticsPerformanceVo::getTime));
        return list;
    }

    @Override
    public IPage<JbStatisticsPerformanceVo> businessPersonageList(PageParam param, JbStatisticsPerformanceVo jbPerformance)
    {
        String time=StringUtils.EMPTY;
        if (StringUtils.isNotBlank(param.getStartTimeBuilder())
        && StringUtils.isNotBlank(param.getEndTimeBuilder()))
        {
            time=StringUtils.format("and create_time>='{}' and create_time<='{}'"
            ,param.getStartTimeBuilder(),param.getEndTimeBuilder());
        }
        //财务审批完的订单
        StringJoiner sql=new StringJoiner("");
        sql.add(StringUtils.format("""
                select\s
                	su.user_name
                	,su.user_id
                	,(select count(1) from jb_customer where del_flag=0 and  business_manager_id=su.user_id
                		{}) as customerAdd
                	,(select count(1) from jb_followup_record where del_flag=0 and staff_id=su.user_id
                		 {})  as followUpAdd
                	,(select SUM(order_total_amount) from jb_order where del_flag=0 and is_process_end='1' and create_by=su.user_id
                		{}) as orderAdd
                from\s
                	sys_user su\s
                where su.del_flag=0
                	order by orderAdd desc
                """,time,time,time));
        String s = joSqlUtil.sqlJson(sql.toString(), param);
        IPage<JbStatisticsPerformanceVo> page = joSqlUtil.jsonToEntity(baseMapper.pageJson(param.b(), s), JbStatisticsPerformanceVo.class);
        AtomicInteger serialNumber = new AtomicInteger(1 + (param.getPageNum() - 1) * param.getPageSize());
        page.getRecords().forEach(a ->
        {
            a.setRanking(serialNumber.getAndIncrement());
        });
        return page;
    }

    /**
     * 商机，客户，订单，时间维度统计，
     * 条件为部门id，多个用户id，开始时间，结束时间
     * @param param
     * @param jbPerformance
     * @param name
     * @param last
     * @return
     */
    private List<JbStatisticsPerformanceVo> appBusinessTimeListSql(PageParam param
            , JbStatisticsPerformanceVo jbPerformance, String name, String last)
    {
        StringJoiner sql = new StringJoiner("");
        sql.add("""
                SELECT
                	DATE_FORMAT( jo.create_time, '%Y-%m' ) AS time,
                	count( 1 ) AS totalCount
                """);
        sql.add(String.format("""
                FROM
                	%s jo
                	INNER JOIN sys_user su ON jo.create_by = su.user_id
                	INNER JOIN sys_dept sd ON su.dept_id = sd.dept_id\s
                WHERE
                	jo.del_flag = 0\s
                	AND su.del_flag = 0\s
                	AND sd.del_flag = 0\s
                """, name));
        sql.add(joSqlUtil.sqlIf(String.format(" AND sd.dept_id=%s", jbPerformance.getDeptId())
                , jbPerformance.getDeptId()));
        if (ObjectUtils.isNotEmpty(jbPerformance.getUserIds()))
        {
            sql.add(joSqlUtil.sqlIf(String.format(" AND jo.create_by IN (%s)", String.join(",", jbPerformance.getUserIds()
                            .stream().map(String::valueOf).collect(Collectors.toList())))
                    , jbPerformance.getUserIds()));
        }
        sql.add(joSqlUtil.sqlIf(String.format(" AND jo.create_time >= '%s'", param.getStartTimeBuilder()), param.getStartTimeBuilder()));
        sql.add(joSqlUtil.sqlIf(String.format(" AND jo.create_time <= '%s'", param.getEndTimeBuilder()), param.getEndTimeBuilder()));
        sql.add(joSqlUtil.sqlIf(last, last));
        sql.add(" GROUP BY time");
        return joSqlUtil.jsonToEntity(baseMapper.findJson(sql.toString()), JbStatisticsPerformanceVo.class);
    }

    /**
     * 提成sql
     *
     * @param jbUserCommissionVo
     * @return
     */
    private String getCommissionVoSql(JbUserCommissionVo jbUserCommissionVo)
    {
        //已完成和已结束订单
        StringJoiner sql = new StringJoiner(" ")
                .add("""
                        select\s
                            su.user_name,sd.dept_name,su.user_id,su.dept_id
                            ,joOrder.*
                        from\s
                            sys_user su
                        LEFT JOIN sys_dept sd on su.dept_id=sd.dept_id
                        LEFT JOIN (
                                select\s
                                jo.create_by
                                ,DATE_FORMAT( jo.create_time, '%Y-%m' ) as yearMonth
                                ,count(jo.id) as orderAdd
                                ,SUM(order_total_amount) as orderTotalAmount
                                ,SUM(received_amount + deposit_amount) as orderReceivedAmount
                                ,SUM(commission_amount) as commissionAmount
                                from  jb_order jo
                                where jo.del_flag=0  and jo.is_process_end='1'
                                """);
        sql.add(joSqlUtil.sqlIf(" and DATE_FORMAT( jo.create_time, '%Y-%m' )" + String.format(" ='%s'"
                , jbUserCommissionVo.getYearMonth()), jbUserCommissionVo.getYearMonth()));
        sql.add("""
                		GROUP BY jo.create_by,yearMonth
                ) joOrder on su.user_id=joOrder.create_by
                where su.del_flag=0
                """);
        sql.add(joSqlUtil.sqlIf(String.format(" and su.user_id=%s", jbUserCommissionVo.getUserId())
                , jbUserCommissionVo.getUserId()));
        if (ObjUtil.isNotNull(jbUserCommissionVo.getDeptId()))
        {
            String deptSql=String.format("select dept_id from sys_dept where dept_id=%s or find_in_set(%s, ancestors )"
                    ,jbUserCommissionVo.getDeptId(),jbUserCommissionVo.getDeptId());
            sql.add(String.format(" and su.dept_id in (%s)",deptSql));
        }
        // sql.add(joSqlUtil.sqlIf(String.format(" and su.dept_id=%s", jbUserCommissionVo.getDeptId())
        //         , jbUserCommissionVo.getDeptId()));
        //角色数据权限
        String roleData=sysDataScopeFactoryService.fnStrDataScope(Constants.PUBLIC_NUMBER_0.toString());
        if (StrUtil.isNotBlank(roleData))
        {
            sql.add(String.format(" and su.user_id in (%s)", roleData));
        }

        // 成交金额倒叙
        sql.add(" order by joOrder.orderTotalAmount desc ");

        return sql.toString();
    }

    /**
     * 业绩统计查询和导出公用sql
     *
     * @param jbPerformance
     * @return
     */
    private String getUserPerformanSql(JbUserPerformanceVo jbPerformance)
    {
        // ui图上没有本财年，上财年等，改成了选择时间框
//        LocalDateTime[] time = getTime(jbPerformance);
        StringJoiner sql = new StringJoiner(" ");
        sql.add("""
                select t.*
                ,CAST(ROUND(if(followUpAdd=0, customerAdd,customerAdd/followUpAdd),2)*100  AS SIGNED) as conversionRate
                from (
                SELECT
                su.user_name,sd.dept_name,su.user_id,su.dept_id
                """);
        sql.add(getSqlData(jbPerformance));
        sql.add(""" 
                 from 
                sys_user su
                LEFT JOIN sys_dept sd on su.dept_id=sd.dept_id
                where su.del_flag=0\s
                """);
        sql.add(joSqlUtil.sqlIf(String.format(" and su.user_id=%s", jbPerformance.getUserId()), jbPerformance.getUserId()));
        if (ObjUtil.isNotNull(jbPerformance.getDeptId()))
        {
            String deptSql=String.format("select dept_id from sys_dept where dept_id=%s or find_in_set(%s, ancestors )"
            ,jbPerformance.getDeptId(),jbPerformance.getDeptId());
            sql.add(String.format(" and su.dept_id in (%s)",deptSql));
        }

        // sql.add(joSqlUtil.sqlIf(String.format(" and su.dept_id=%s", jbPerformance.getDeptId()), jbPerformance.getDeptId()));
        //角色数据权限
        String roleData=sysDataScopeFactoryService.fnStrDataScope(Constants.PUBLIC_NUMBER_0.toString());
        if (StrUtil.isNotBlank(roleData))
        {
            sql.add(String.format(" and su.user_id in (%s)", roleData));
        }
        sql.add(") t ");
        sql.add(joSqlUtil.sqlIf(String.format(" order by %s %s",
                        "conversionRate".equals(jbPerformance.getOrderKey()) ? jbPerformance.getOrderKey()
                                : "t." + jbPerformance.getOrderKey(),
                        StringUtils.isBlank(jbPerformance.getOrderValue()) ? "desc" : jbPerformance.getOrderValue())
                , jbPerformance.getOrderKey()));
        return sql.toString();
    }

    /**
     * 业绩各维度统计的sql
     *
     * @param jbPerformance
     * @return
     */
    private String getSqlData(JbUserPerformanceVo jbPerformance)
    {
        //财务审批完的订单
        StringJoiner joiner = new StringJoiner("");
        if (StringUtils.isNotBlank(jbPerformance.getSTime()) && StringUtils.isNotBlank(jbPerformance.getETime()))
        {
//            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
//            String sFormat = time[0].format(formatter);
//            String eFormat = time[1].format(formatter);
            String sFormat = jbPerformance.getSTime();
            String eFormat = jbPerformance.getETime();
            joiner.add(String.format("""
                            ,(select count(1) from jb_customer where del_flag=0 and  business_manager_id=su.user_id\s
                            and create_time>='%s' and create_time<='%s') as customerAdd
                            ,(select count(1) from jb_followup_record where del_flag=0 and staff_id=su.user_id\s
                            and data_type=1 and create_time>='%s' and create_time<='%s')  as followUpAdd
                            ,(select count(DISTINCT(customer_id)) from jb_order where del_flag=0 and create_by=su.user_id\s
                              and create_time>='%s' and create_time<='%s' and is_process_end='1') as customerTrading
                            ,(select count(1) from jb_order where del_flag=0 and create_by=su.user_id\s
                              and create_time>='%s' and create_time<='%s' and is_process_end='1') as orderAdd
                            ,(select SUM(order_total_amount) from jb_order where del_flag=0 and create_by=su.user_id\s
                            and create_time>='%s' and create_time<='%s' and is_process_end='1') as orderTotalAmount
                            ,(select SUM(received_amount+deposit_amount) from jb_order where del_flag=0 and create_by=su.user_id\s
                            and create_time>='%s' and create_time<='%s' and is_process_end='1') as orderReceivedAmount
                            """, sFormat, eFormat, sFormat, eFormat, sFormat, eFormat, sFormat, eFormat
                    , sFormat, eFormat, sFormat, eFormat)
            );
        } else
        {
            joiner.add("""
                    ,(select count(1) from jb_customer where del_flag=0 and  business_manager_id=su.user_id\s
                    ) as customerAdd
                    ,(select count(1) from jb_followup_record where del_flag=0 and staff_id=su.user_id\s
                    )  as followUpAdd
                    ,(select count(DISTINCT(customer_id)) from jb_order where del_flag=0 and is_process_end='1' and create_by=su.user_id\s
                     ) as customerTrading
                    ,(select count(1) from jb_order where del_flag=0 and is_process_end='1' and create_by=su.user_id\s
                     ) as orderAdd
                    ,(select SUM(order_total_amount) from jb_order where del_flag=0 and is_process_end='1' and create_by=su.user_id\s
                    ) as orderTotalAmount
                    ,(select SUM(received_amount+deposit_amount) from jb_order where del_flag=0 and is_process_end='1' and create_by=su.user_id\s
                    ) as orderReceivedAmount
                    """);
        }

        return joiner.toString();
    }
}
